package DataAccess;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class SqlLiteDatabaseHandler extends SQLiteOpenHelper{

	//All Static variables 
	//Database Version 
	public static final int DATABASE_VERSION = 3; 
	 
	//Database Name 
	public static final String DATABASE_NAME = "building_inout.s3db"; 
	 
	//----------Account_ table name -------------
	public static final String TABLE_ACCOUNT_ = "Account_"; 
	//Account_ Table Columns names 
	public static final String KEY_ACOUTID = "AcountID"; 
	 
	public static final String KEY_ACCOUNTNAME = "AccountName"; 
	 
	public static final String KEY_CUS_ID = "Cus_ID"; 
	 
	public static final String KEY_AVATA = "Avata"; 
	 
	public static final String KEY_PHOTO = "Photo"; 
	 
	public static final String KEY_DATEPOST = "datepost"; 
	 
	public static final String KEY_NOTE = "note"; 
	 
	public static final String KEY_FLAG = "flag"; 
	 
	public static final String KEY_USERID = "UserID"; 
	 
	//----------IdentityCard_put table name -------------
	public static final String TABLE_IDENTITYCARD_PUT = "IdentityCard_put"; 
	//IdentityCard_put Table Columns names 
	public static final String KEY_ID = "ID"; 
	 
	public static final String KEY_DECKID = "deckID"; 
	 
	public static final String KEY_INDEX = "[index]"; 
	 
	//private static final String KEY_ACOUTID = "AcoutID"; 
	 
	
	//----------Gate_ table name -------------
	public static final String TABLE_GATE_ = "Gate_"; 
	//Gate_ Table Columns names 
	//private static final String KEY_ID = "ID"; 
	 
	public static final String KEY_NAME = "Name"; 
	 
	public static final String KEY_IPCAMERA = "IPCamera"; 
	 
	//private static final String KEY_FLAG = "flag"; 
	 
	//----------Users_ table name -------------
	public static final String TABLE_USERS_ = "Users_"; 
	//Users_ Table Columns names 
	//private static final String KEY_ID = "ID"; 
	 
	public static final String KEY_USERNAME = "UserName"; 
	 
	public static final String KEY_PASSWORD = "Password"; 
	 
	public static final String KEY_FULLNAME = "Fullname"; 
	 
	public static final String KEY_CMND = "CMND"; 
	 
	public static final String KEY_ROLE = "Role"; 
	 
	//private static final String KEY_FLAG = "flag"; 
	 
	//----------Customer_ table name -------------
	public static final String TABLE_CUSTOMER_ = "Customer_"; 
	//Customer_ Table Columns names 
	//private static final String KEY_ID = "ID"; 
	 
	//private static final String KEY_FULLNAME = "Fullname"; 
	 
	public static final String KEY_SEX = "Sex"; 
	 
	public static final String KEY_BIRTHDAY = "Birthday"; 
	 
	public static final String KEY_BORN = "Born"; 
	 
	//private static final String KEY_CMND = "CMND"; 
	 
	public static final String KEY_ADDRESS = "Address"; 
	 
	//private static final String KEY_DATEPOST = "datepost"; 
	 
	//----------Card_ table name -------------
	public static final String TABLE_CARD_ = "Card_"; 
	//Card_ Table Columns names 
	public static final String KEY_CARDID = "CardID"; 
	 
	//private static final String KEY_DATEPOST = "datepost"; 
	 
	//private static final String KEY_FLAG = "flag"; 
	 
	//private static final String KEY_NOTE = "note"; 
	 
	//private static final String KEY_ACOUTID = "AcoutID"; 
	 
	//----------IdentityCard_deck table name -------------
	public static final String TABLE_IDENTITYCARD_DECK = "IdentityCard_deck"; 
	//IdentityCard_deck Table Columns names 
	//private static final String KEY_ID = "ID"; 
	 
	public static final String KEY_DECKNAME = "deckName"; 
	 
	public static final String KEY_QUANTITY = "Quantity"; 
	 
	//private static final String KEY_DATEPOST = "datepost"; 
	 
	//private static final String KEY_FLAG = "flag"; 
	 
	//private static final String KEY_NOTE = "note"; 
	 
	//----------Come_in table name -------------
	public static final String TABLE_COME_IN = "Come_in"; 
	//Come_in Table Columns names 
	//private static final String KEY_ID = "ID"; 
	 
	public static final String KEY_TIME_IN = "Time_In"; 
	 
	//private static final String KEY_ACOUTID = "AcoutID"; 
	 
	public static final String KEY_GATEID = "GateID"; 
	 
	//private static final String KEY_USERID = "UserID"; 
	 
	//----------Out_side table name -------------
	public static final String TABLE_OUT_SIDE = "Out_side"; 
	//Out_side Table Columns names 
	//private static final String KEY_ID = "ID"; 
	 
	//private static final String KEY_GATEID = "GateID"; 
	 
	//private static final String KEY_TIME_IN = "Time_In"; 
	 
	public static final String KEY_TIME_OUT = "Time_out"; 
	 
	//private static final String KEY_ACOUTID = "AcoutID"; 
	 
	//----------Identity_log table name -------------
	public static final String TABLE_IDENTITY_LOG = "Identity_log"; 
	
	private static final String TABLE_CardKey ="CardKey";
	private static final String TABLE_CardName ="CardName";
	//Identity_log Table Columns names 
	//private static final String KEY_ID = "ID"; 
	 
	//private static final String KEY_DECKID = "deckID"; 
	 
	//private static final String KEY_INDEX = "index"; 
	 
	//private static final String KEY_ACOUTID = "AcoutID"; 
	 
	public SqlLiteDatabaseHandler(Context context) { 
		super(context, DATABASE_NAME, null, DATABASE_VERSION); 
	 } 
	 
	// Creating Tables 
	@Override 
	public void onCreate(SQLiteDatabase db) {

		try { 
			 db.execSQL("CREATE TABLE "+ TABLE_ACCOUNT_+" (" +  KEY_ACOUTID+" INTEGER PRIMARY KEY AUTOINCREMENT," +  KEY_ACCOUNTNAME + " NVARCHAR," +  KEY_CUS_ID + " INTEGER," +  KEY_AVATA + " NVARCHAR," +  KEY_PHOTO + " NVARCHAR," +  KEY_DATEPOST + " DATE," +  KEY_NOTE + " NVARCHAR," +  KEY_FLAG + " BOOLEAN," +  KEY_USERID+" INTEGER);"); 
			} catch (Exception e) { 
			 Log.v("Bru_Press_Data","exception in table ACCOUNT_ created"); 
			 } 

			 try { 
			 db.execSQL("CREATE TABLE "+ TABLE_IDENTITYCARD_PUT+" (" +  KEY_ID+" INTEGER PRIMARY KEY AUTOINCREMENT," +  KEY_DECKID + " INTEGER," +  KEY_INDEX + " INTEGER," +  KEY_ACOUTID+" INTEGER);"); 
			} catch (Exception e) { 
			 Log.v("Bru_Press_Data","exception in table IDENTITYCARD_PUT created"); 
			 } 

			

			 try { 
			 db.execSQL("CREATE TABLE "+ TABLE_GATE_+" (" +  KEY_ID+" INTEGER PRIMARY KEY AUTOINCREMENT," +  KEY_NAME + " NVARCHAR," +  KEY_IPCAMERA + " NVARCHAR," +  KEY_FLAG+" BOOLEAN);"); 
			} catch (Exception e) { 
			 Log.v("Bru_Press_Data","exception in table GATE_ created"); 
			 } 

			 try { 
			 db.execSQL("CREATE TABLE "+ TABLE_USERS_+" (" +  KEY_ID+" INTEGER PRIMARY KEY AUTOINCREMENT," +  KEY_USERNAME + " NVARCHAR," +  KEY_PASSWORD + " NVARCHAR," +  KEY_FULLNAME + " NVARCHAR," +  KEY_CMND + " NVARCHAR," +  KEY_ROLE + " NVARCHAR," +  KEY_FLAG+" BOOLEAN);"); 
			} catch (Exception e) { 
			 Log.v("Bru_Press_Data","exception in table USERS_ created"); 
			 } 

			 try { 
			 db.execSQL("CREATE TABLE "+ TABLE_CUSTOMER_+" (" +  KEY_ID+" INTEGER PRIMARY KEY AUTOINCREMENT," +  KEY_FULLNAME + " NVARCHAR," +  KEY_SEX + " NVARCHAR," +  KEY_BIRTHDAY + " DATE," +  KEY_BORN + " NVARCHAR," +  KEY_CMND + " NVARCHAR," +  KEY_ADDRESS + " NVARCHAR," +  KEY_DATEPOST+" DATE);"); 
			} catch (Exception e) { 
			 Log.v("Bru_Press_Data","exception in table CUSTOMER_ created"); 
			 } 

			 try { 
			 db.execSQL("CREATE TABLE "+ TABLE_CARD_+" (" +  KEY_CARDID+" INTEGER PRIMARY KEY AUTOINCREMENT," +  KEY_DATEPOST + " DATE," +  KEY_FLAG + " BOOLEAN," +  KEY_NOTE + " NVARCHAR," +  KEY_ACOUTID+" INTEGER,"+ TABLE_CardKey+" NVARCHAR,"+TABLE_CardName+" NVARCHAR);"); 
			} catch (Exception e) { 
			 Log.v("Bru_Press_Data","exception in table CARD_ created"); 
			 } 

			 try { 
			 db.execSQL("CREATE TABLE "+ TABLE_IDENTITYCARD_DECK+" (" +  KEY_ID+" INTEGER PRIMARY KEY AUTOINCREMENT," +  KEY_DECKNAME + " NVARCHAR," +  KEY_QUANTITY + " INTEGER," +  KEY_DATEPOST + " DATE," +  KEY_FLAG + " BOOLEAN," +  KEY_NOTE+" NVARCHAR);"); 
			} catch (Exception e) { 
			 Log.v("Bru_Press_Data","exception in table IDENTITYCARD_DECK created"); 
			 } 

			 try { 
			 db.execSQL("CREATE TABLE "+ TABLE_COME_IN+" (" +  KEY_ID+" INTEGER PRIMARY KEY AUTOINCREMENT," +  KEY_TIME_IN + " DATE," +  KEY_ACOUTID + " INTEGER," +  KEY_GATEID + " INTEGER," +  KEY_USERID+" INTEGER);"); 
			} catch (Exception e) { 
			 Log.v("Bru_Press_Data","exception in table COME_IN created"); 
			 } 

			 try { 
			 db.execSQL("CREATE TABLE "+ TABLE_OUT_SIDE+" (" +  KEY_ID+" INTEGER PRIMARY KEY AUTOINCREMENT," +  KEY_GATEID + " INTEGER," +  KEY_TIME_IN + " DATE," +  KEY_TIME_OUT + " DATE," +  KEY_ACOUTID+" INTEGER);"); 
			} catch (Exception e) { 
			 Log.v("Bru_Press_Data","exception in table OUT_SIDE created"); 
			 } 

			 try { 
			 db.execSQL("CREATE TABLE "+ TABLE_IDENTITY_LOG+" (" +  KEY_ID+" INTEGER PRIMARY KEY AUTOINCREMENT," +  KEY_DECKID + " INTEGER," +  KEY_INDEX + " INTEGER," +  KEY_ACOUTID+" INTEGER," +KEY_USERID+" INTEGER);"); 
			} catch (Exception e) { 
			 Log.v("Bru_Press_Data","exception in table IDENTITY_LOG created"); 
			 } 

	} 
	 
	// Upgrading database 
	@Override 
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	db.execSQL("DROP TABLE IF EXISTS "+TABLE_ACCOUNT_); 
	db.execSQL("DROP TABLE IF EXISTS "+TABLE_IDENTITYCARD_PUT); 

	db.execSQL("DROP TABLE IF EXISTS "+TABLE_GATE_); 
	db.execSQL("DROP TABLE IF EXISTS "+TABLE_USERS_); 
	db.execSQL("DROP TABLE IF EXISTS "+TABLE_CUSTOMER_); 
	db.execSQL("DROP TABLE IF EXISTS "+TABLE_CARD_); 
	db.execSQL("DROP TABLE IF EXISTS "+TABLE_IDENTITYCARD_DECK); 
	db.execSQL("DROP TABLE IF EXISTS "+TABLE_COME_IN); 
	db.execSQL("DROP TABLE IF EXISTS "+TABLE_OUT_SIDE); 
	db.execSQL("DROP TABLE IF EXISTS "+TABLE_IDENTITY_LOG); 
	  
	 onCreate(db); 
	 }

	
	
	
}
